package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modellayer.Item;

public class DBItem implements IFDBItem {

	private Connection con;
	private DBType type;
	private DBSupplier supplier;
	
	public DBItem(){
		con = DBConnection.getInstance().getDBcon();
		type = new DBType();
		supplier = new DBSupplier();
	}
	
	@Override
	public ArrayList<Item> getAllItems() {
		// TODO Auto-generated method stub
		return miscWhere("");
	}

	@Override
	public Item searchByID(int ID) {
		// TODO Auto-generated method stub
		String query = " id = '" + ID + "'";
		return singleWhere(query);
	}

	@Override
	public Item searchByName(String name) {
		// TODO Auto-generated method stub
		String query = " name = '" + name + "'";
		return singleWhere(query);
	}

	@Override
	public Item searchByDescription(String description) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Item searchByPrice(double price) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Item searchByAmount(int amount) {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public Item searchByStatus(String status){
		String query = " id = '" + status + "'";
		return singleWhere(query);
	}

	@Override
	public Item searchByMax(int max) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Item searchByMin(int min) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Item searchByType(int type) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Item searchBySupplier(int supplier) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int insertItem(Item item) throws Exception {
		// TODO Auto-generated method stub
		int nextID = GetMax.getMaxID("SELECT max(id) FROM Items");
		nextID = nextID + 1;
		
		int rc = -1;
		
		String query = "INSERT INTO Items(id, name, description, price, amount, status, maxSupply, minSupply, typeID, supplierID) VALUES('"
				+ nextID + "','"
				+ item.getName() + "','"
				+ item.getDescription() + "','"
				+ item.getPrice() + "','"
				+ item.getAmount() + "','"
				+ item.getStatus() + "','"
				+ item.getMax() + "','"
				+ item.getMin() + "','"
				+ item.getType().getID() + "','"
				+ item.getSupplier().getID() + "')";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException e){
			System.out.println("Error : Exception catched in insertItem. " + e);
			e.printStackTrace();
		}
		
		return rc;
	}

	@Override
	public int updateItem(Item item) {
		// TODO Auto-generated method stub
		int rc = -1;
		
		String query = "UPDATE Item SET "
				+ "name = '" + item.getName() + "',"
				+ "description = '" + item.getDescription() + "',"
				+ "price = '" + item.getPrice() + "',"
				+ "amount = '" + item.getAmount() + "',"
				+ "status = '" + item.getStatus() + "',"
				+ "maxSupply = '" + item.getMax() + "',"
				+ "minSupply = '" + item.getMin() + "',"
				+ "typeID = '" + item.getType().getID() + "',"
				+ "supplierID = '" + item.getSupplier().getID() + "'"
				+ " WHERE id = '" + item.getID() + "'";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.out.println("Error : Exception catched in updateItem. " + e);
			e.printStackTrace();
		}
		
		return rc;
	}

	@Override
	public int deleteItem(int id) {
		// TODO Auto-generated method stub
		int rc = -1;
		
		String query = "DELETE FROM Items WHERE id = '" + id + "'";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.out.println("Error : Exception catched in deleteItem. " + e);
			e.printStackTrace();
		}
		
		return rc;
	}

	/////////////////////////////////////////////////////////////////////////////////////////
	/////////////////////////// Methods outside the interface ///////////////////////////////
	/////////////////////////////////////////////////////////////////////////////////////////
	
	private ArrayList<Item> miscWhere(String wClause){
		ResultSet result;
		ArrayList<Item> list = new ArrayList<Item>();
		
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			while(result.next()){
				Item item = new Item();
				item = buildItem(result);
				list.add(item);
			}
			
			stmt.close();
		}
		catch(Exception e){
			System.out.println("Error : Exception catched in miscWhere. " + e);
			e.printStackTrace();
		}
		
		return list;
	}
	
	private Item singleWhere(String wClause){
		ResultSet result;
		Item item = new Item();
		
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next())
				item = buildItem(result);
			else
				item = null;
			
			stmt.close();
		}
		catch(Exception e){
			System.out.println("Error : Exception catched in singleWhere. " +e);
			e.printStackTrace();
		}
		
		
		return item;
	}
	
	private String buildQuery(String wClause){
		String query = "SELECT id, name, description, price, amount, status, maxSupply, minSupply, typeID, supplierID FROM Items";
		
		if (wClause.length()>0)
			query = query + " WHERE " + wClause;
		
		return query;
	}
	
	private Item buildItem(ResultSet result){
		Item item = new Item();
		
		try{
			item.setID(result.getInt("id"));
			item.setName(result.getString("name"));
			item.setDescription(result.getString("description"));
			item.setPrice(result.getDouble("price"));
			item.setAmount(result.getInt("amount"));
			item.setStatus(result.getString("status"));
			item.setMax(result.getInt("maxSupply"));
			item.setMin(result.getInt("minSupply"));
			item.setType(type.searchByID(result.getInt("typeID")));
			item.setSupplier(supplier.searchByID(result.getInt("supplierID")));
		}
		catch(Exception e){
			System.out.println("Error : Exception catched in buildItem. " + e);
			e.printStackTrace();
		}
		
		return item;
	}
}
